package com.person.jc.service.test.assign;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.person.jc.common.Constants;
import com.person.jc.common.SQLSpell;
import com.person.jc.service.Service;
import com.person.jc.utils.Utils;

public class BsTaskAssignService extends Service {
	
	public final static BsTaskAssignService me = new BsTaskAssignService();
	
	public List<Record> getList(Map<String, Object> map){
		String temp = "";
		List<Object> params = new ArrayList<Object>();
		String tempSql = "SELECT DISTINCT\n" +
				"	t.order_id,\n" +
				"	t.order_no,\n" +
				"	(SELECT cust_name FROM bd_customer WHERE t.cust_id = cust_id) cust_name,\n" +
				"	t.team_no,\n" +
				"	t.receive_date,\n" +
				"	t.service_user,\n" +
				"	("+SQLSpell.userName("t.service_user")+") service_user_,\n" +
				"	(SELECT COUNT(DISTINCT user_id) FROM bs_task_assign_user WHERE assign_id = t1.assign_id) assign_count,\n" +
				"	(SELECT user_name FROM ts_user where t1.leader_id = user_id) leader_name,\n" +
				"	DATE_FORMAT(t1.complete_date, '%Y-%m-%d') complete_date,\n" +
				"	t1.assign_id\n" +
				"FROM\n" +
				"	bs_order t\n" +
				"LEFT JOIN bs_task_assign t1 ON t.order_id = t1.order_id\n" +
				"LEFT JOIN bs_task_assign_user t2 ON t.order_id = t2.order_id\n" +
				"LEFT JOIN ts_user t3 ON t2.user_id = t3.user_id\n" +
				"WHERE t.order_type = ?\n" ;
		StringBuffer sql = new StringBuffer(tempSql);
		params.add(map.get("orderType"));
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("orderId")))){
			sql.append(" and t.order_id like ? ");
			params.add("%" + temp + "%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("orderNo")))){
			sql.append(" and t.order_no like ? ");
			params.add("%" + temp + "%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("custId")))){
			sql.append(" and t.cust_id = ? ");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("teamNo")))){
			sql.append(" and t.team_no like ? ");
			params.add("%" + temp + "%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("startDate")))){
			sql.append(" and DATE_FORMAT(t.opt_date,'%Y-%m-%d') >= ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("endDate")))){
			sql.append(" and DATE_FORMAT(t.opt_date,'%Y-%m-%d') <= ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("tester")))){
			sql.append(" and t3.user_name like ? ");
			params.add("%" + temp + "%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("startDate1")))){
			sql.append(" and DATE_FORMAT(t1.complete_date,'%Y-%m-%d') >= ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("endDate1")))){
			sql.append(" and DATE_FORMAT(t1.complete_date,'%Y-%m-%d') <= ?");
			params.add(temp);
		}
		sql.append(" order by t.opt_date desc");
		return Db.find(sql.toString(), Utils.getObjParam(params));
	}
	
	public List<Record> getList2(Map<String, Object> map){
		String temp = "";
		List<Object> params = new ArrayList<Object>();
		String tempSql = "select DISTINCT ot.*, ("+SQLSpell.customerName("ot.cust_id")+") cust_id_, " +
							"("+SQLSpell.userName("ot.leader_id")+") leader_id_, " +
							"("+SQLSpell.userName("ot.service_user")+") service_user_\n" +
							"  from (SELECT t.order_id,\n" +
							"               t.order_no,\n" +
							"               t.cust_id,\n" +
							"               t.team_no,\n" +
							"               t.receive_date,\n" +
							"               t.opt_date,\n" +
							"               t.service_user,\n" +
							"               (SELECT COUNT(DISTINCT user_id) FROM bs_task_assign_user  WHERE assign_id = t1.assign_id) assign_count,\n" +
							"               t1.leader_id,\n" +
							"               t1.complete_date,\n" +
							"               t1.assign_id,\n" +
							"               'jc' type\n" +
							"          FROM bs_order t\n" +
							"          LEFT JOIN bs_task_assign t1\n" +
							"            ON t.order_id = t1.order_id\n" +
							"           and t1.order_type = 2\n" +
							"         WHERE t.order_type = 2\n" +
							"           and t.flow_id = 0\n" +
							"        \n" +
							"        UNION all\n" +
							"        \n" +
							"        select o.order_id,\n" +
							"               o.order_no,\n" +
							"               o.cust_id,\n" +
							"               o.team_no,\n" +
							"               o.receive_date,\n" +
							"               o.opt_date,\n" +
							"               o.service_user,\n" +
							"               (SELECT COUNT(DISTINCT user_id) FROM bs_task_assign_user WHERE assign_id = ta.assign_id) assign_count,\n" +
							"               ta.leader_id,\n" +
							"               ta.complete_date,\n" +
							"               ta.assign_id,\n" +
							"               'wx' type\n" +
							"          from bs_parts_confirm_log pc\n" +
							"          left join bs_repair_reg_log rl\n" +
							"            on pc.log_id = rl.log_id\n" +
							"          left join bs_order o\n" +
							"            on rl.order_id = o.order_id\n" +
							"          left join bs_order_equipment oe\n" +
							"            on rl.order_id = oe.order_id\n" +
							"           and rl.equ_id = oe.equ_id\n" +
							"          LEFT JOIN bs_task_assign ta\n" +
							"            ON o.order_id = ta.order_id\n" +
							"           and ta.order_type = 3\n" +
							"         where pc.confirm_status = 1 and o.order_type = 2 and o.flow_id = "+Constants.FLOW_WX_PJQR+") ot\n" +
							"  LEFT JOIN bs_task_assign_user t2 ON ot.order_id = t2.order_id\n" +
							"  LEFT JOIN ts_user t3 ON t2.user_id = t3.user_id\n" +
							"	where 1=1\n";

		StringBuffer sql = new StringBuffer(tempSql);
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("orderId")))){
			sql.append(" and ot.order_id like ? ");
			params.add("%" + temp + "%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("orderNo")))){
			sql.append(" and ot.order_no like ? ");
			params.add("%" + temp + "%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("custId")))){
			sql.append(" and ot.cust_id = ? ");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("teamNo")))){
			sql.append(" and ot.team_no like ? ");
			params.add("%" + temp + "%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("startDate")))){
			sql.append(" and DATE_FORMAT(ot.opt_date,'%Y-%m-%d') >= ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("endDate")))){
			sql.append(" and DATE_FORMAT(ot.opt_date,'%Y-%m-%d') <= ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("tester")))){
			sql.append(" and t3.user_name like ? ");
			params.add("%" + temp + "%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("startDate1")))){
			sql.append(" and DATE_FORMAT(ot.complete_date,'%Y-%m-%d') >= ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("endDate1")))){
			sql.append(" and DATE_FORMAT(ot.complete_date,'%Y-%m-%d') <= ?");
			params.add(temp);
		}
		sql.append("  order by ot.complete_date, ot.type");
		return Db.find(sql.toString(), Utils.getObjParam(params));
	}
	
	public List<Record> getInfo(String orderId, String orderType, String type){
		String sql = "select o.order_id, o.cust_id,("+SQLSpell.customerName("o.cust_id")+") cust_name, oe.device_type deviceType," +
				" count(oe.device_type) nums, ta.leader_id, DATE_FORMAT(ta.complete_date,'%Y-%m-%d') complete_date, tau.* from bs_order o \n" +
				"left join bs_order_equipment oe on o.order_id = oe.order_id \n" +
				"left join bs_task_assign ta on o.order_id = ta.order_id and ta.order_type = ?\n" +
				"left join (SELECT au.assign_id,\n" +
				"                  au.device_type,\n" +
				"                  group_concat(au.user_id) user_id,\n" +
				"                  GROUP_CONCAT(u.user_name SEPARATOR '，') user_name\n" +
				"             FROM bs_task_assign_user au\n" +
				"             LEFT JOIN ts_user u\n" +
				"               ON au.user_id = u.user_id\n" +
				"            group by au.assign_id, au.device_type) tau\n" +
				"    on ta.assign_id = tau.assign_id and oe.device_type = tau.device_type\n" +
				"where o.order_id = ?\n" +
				"group by o.order_id, o.cust_id, oe.device_type";
		
		if("wx".equals(type)){
			sql = "select rl.order_id, rl.cust_id,("+SQLSpell.customerName("rl.cust_id")+") cust_name, oe.device_type deviceType, count(oe.device_type) nums,ta.leader_id, \n" +
					"	DATE_FORMAT(ta.complete_date,'%Y-%m-%d') complete_date, \n" +
					"	tau.*   from bs_repair_reg_log rl\n" +
					"left join bs_repair_confirm_log rc on rl.log_id = rc.log_id\n" +
					"left join bs_parts_confirm_log pc on rl.log_id = pc.log_id\n" +
					"left join bs_order_equipment oe on rl.equ_id = oe.equ_id\n" +
					"left join bs_task_assign ta on rl.order_id = ta.order_id and ta.order_type = ?\n" +
					"left join (SELECT au.assign_id,\n" +
					"                  au.device_type,\n" +
					"                  group_concat(au.user_id) user_id,\n" +
					"                  GROUP_CONCAT(u.user_name SEPARATOR '，') user_name\n" +
					"             FROM bs_task_assign_user au\n" +
					"             LEFT JOIN ts_user u\n" +
					"               ON au.user_id = u.user_id\n" +
					"            group by au.assign_id, au.device_type) tau\n" +
					"    on ta.assign_id = tau.assign_id and oe.device_type = tau.device_type\n" +
					"where rc.confirm_status = 1 and pc.confirm_status = 1\n" +
					"and rl.order_id = ?\n" +
					"group by rl.order_id, rl.cust_id, oe.device_type";
			orderType = "3";
		}
		return Db.find(sql, orderType, orderId);
		
	}
	
	public List<Record> getLeaderList(Object assignId){
		String sql = "SELECT DISTINCT t1.user_id, t1.user_name FROM bs_task_assign_user t, ts_user t1 WHERE t.user_id = t1.user_id AND  assign_id = ?";
		return Db.find(sql, assignId);
	}
	
	public String save(Map<String, Object> map){
		String sql = "insert into bs_task_assign(order_id, order_type, leader_id, complete_date) values (?, ?, ?, ?)";
		Db.update(sql, map.get("orderId"),map.get("orderType"),map.get("leaderId"),map.get("completeDate"));
		super.setCRUD("任务分配", "C", "新增任务分配："+ map.get("orderId"));
		String idxSql = "SELECT LAST_INSERT_ID() from dual ";
		String id = Db.queryColumn(idxSql).toString();
		return id;
	}
	
	public void update(Map<String, Object> map){
		String sql = " update bs_task_assign set leader_id = ?, complete_date = ? where assign_id = ?";
		Db.update(sql, map.get("leaderId"),map.get("completeDate"),map.get("assignId"));
		super.setCRUD("任务分配", "U", "修改任务分配："+ map.get("orderId"));
	}
	
	public void deleteUser(String orderId){
		String sql = "delete from bs_task_assign_user where order_id = ?";
		Db.update(sql, orderId);
		super.setCRUD("任务分配", "D", "删除任务分配人员："+ orderId);
	}

	public void saveUser(String assignId,String deviceType, String userId, String orderId){
		if(Utils.isNotEmpty(userId)){
			String sql = "insert into bs_task_assign_user(assign_id, order_id, device_type, user_id) values (?, ?, ?, ?)";
			for (String uId : userId.split(",")) {
				Db.update(sql, assignId,orderId, deviceType, uId);
			}
			super.setCRUD("任务分配", "C", "心中任务分配人员："+ orderId);
		}
	}
	
	public List<Record> getUser(String userId){
		userId = Utils.isNotEmpty(userId)?userId.replace(",", "','"):"";
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT t.user_id, t.user_name,t1.dept_name,t2.user_id flag FROM ts_user t");
		sql.append(" LEFT JOIN ts_dept t1 ON t.dept_id = t1.dept_id");
		sql.append(" LEFT JOIN (SELECT user_id FROM ts_user WHERE user_id IN ('"+ userId +"')) t2 ON t.user_id = t2.user_id");
		sql.append(" where t.dept_id like '000104%'");
		sql.append(" and t.user_id not in (select user_id from ts_user_role_vcl where role_id=11)");
		return Db.find(sql.toString());
	}

	// 获取任务信息
	public Record getTaskInfo(String userId) {
		String sql = "SELECT SUM(order_type=1) AS checkNum,SUM(order_type=2) AS repairNum from bs_order WHERE flow_id =0 AND opt_user = ?";
		return Db.findFirst(sql,userId);
	}
}
